﻿Imports MySql.Data.MySqlClient
Imports System.Data.OleDb
Imports System.Data.Odbc
Imports System.Drawing
Imports System.Windows.Forms
Public Class ambulance
    Dim mySql As MySqlConnection
    Dim mysql_pass As String
    Dim ip_connect As String
    Dim user_namedb As String
    Dim db_name As String
    Dim pm_key As String
    Dim dr As String
    Dim rn As String
    Dim pn As String
    Dim na As String
    Dim car As String
    Private Sub ambulance_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        mySql = New MySqlConnection
        'mysql_pass = "stomsite"
        ip_connect = "ryh1"
        'ip_connect = "119.59.99.151"

        'user_namedb = "tmcport_shipping"

        'db_name = "tmcport_shipping"

        '  ip_connect = "127.0.0.1"
        mysql_pass = "software"
        user_namedb = "june"
        db_name = "ambu"

        mySql.ConnectionString = "server=" + ip_connect + ";user id=" + user_namedb + ";password=" + mysql_pass + ";database=" + db_name + ";Character Set =utf8;"
        Try
            mySql.Open()
            '    MsgBox("CONNECTED TO DATABASE")
        Catch ex As Exception
            MsgBox("Can't Connect to database" + ex.Message)

            Me.Close()
        End Try


        Dim mySqlCommand As New MySqlCommand
        Dim mySqlAdaptor As New MySqlDataAdapter
        Dim mySqlReader As MySqlDataReader
        If mySql.State = ConnectionState.Closed Then
            mySql.Open()
        End If

        mySqlCommand.CommandText = "Select Distinct prov from  ambu ;"
        mySqlCommand.Connection = mySql
        mySqlAdaptor.SelectCommand = mySqlCommand
        Try
            mySqlReader = mySqlCommand.ExecuteReader
            While mySqlReader.Read()
                province.Items.Add(mySqlReader("prov"))

            End While

        Catch ex As Exception
            MsgBox(ex.ToString)

            mySql.Close()
        End Try
        mySql.Close()

    End Sub

    Private Sub province_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles province.SelectedIndexChanged
        Dim mySqlCommand As New MySqlCommand
        Dim mySqlAdaptor As New MySqlDataAdapter
        Dim mySqlReader As MySqlDataReader
        If mySql.State = ConnectionState.Closed Then
            mySql.Open()
        End If
        default_fon()

        mySqlCommand.CommandText = "Select Distinct ampur from  ambu where prov ='" & province.Text & "';"
        mySqlCommand.Connection = mySql
        mySqlAdaptor.SelectCommand = mySqlCommand
        amphur.Items.Clear()
        tambol.Items.Clear()

        amphur.Text = ""
        tambol.Text = ""
        Try
            mySqlReader = mySqlCommand.ExecuteReader
            While mySqlReader.Read()
                amphur.Items.Add(mySqlReader("ampur"))

            End While

        Catch ex As Exception
            MsgBox(ex.ToString)

            mySql.Close()
        End Try

        mySql.Close()





        If mySql.State = ConnectionState.Closed Then
            mySql.Open()
        End If


        mySqlCommand.CommandText = "Select COUNT(*) AS count_record from  ambu where prov ='" & province.Text & "';"
        mySqlCommand.Connection = mySql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader
            While mySqlReader.Read()
                If mySqlReader("count_record") = "1" Then
                    Show_price()

                End If
            End While

        Catch ex As Exception

        End Try

        mySql.Close()

    End Sub

    Private Sub amphur_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles amphur.SelectedValueChanged
        default_fon()
        Dim mySqlCommand As New MySqlCommand
        Dim mySqlAdaptor As New MySqlDataAdapter
        Dim mySqlReader As MySqlDataReader
        If mySql.State = ConnectionState.Closed Then
            mySql.Open()
        End If

        mySqlCommand.CommandText = "Select * from  ambu where prov ='" & province.Text & "' and ampur = '" & amphur.Text & "';"
        mySqlCommand.Connection = mySql
        mySqlAdaptor.SelectCommand = mySqlCommand
        tambol.Items.Clear()
        tambol.Text = ""

        Try
            mySqlReader = mySqlCommand.ExecuteReader
            While mySqlReader.Read()
                tambol.Items.Add(mySqlReader("tambul"))
            End While

        Catch ex As Exception
            MsgBox(ex.ToString)

            mySql.Close()
        End Try

        mySql.Close()




        If mySql.State = ConnectionState.Closed Then
            mySql.Open()
        End If

        mySqlCommand.CommandText = "Select COUNT(*) AS count_record from  ambu where prov ='" & province.Text & "' and ampur = '" & amphur.Text & "';"
        mySqlCommand.Connection = mySql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader
            While mySqlReader.Read()
                If mySqlReader("count_record") = "1" Then

                    Show_price1()

                End If
            End While

        Catch ex As Exception

        End Try

        mySql.Close()
    End Sub


    Private Sub tambol_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles tambol.SelectedValueChanged
        default_fon()
        Dim mySqlCommand As New MySqlCommand
        Dim mySqlAdaptor As New MySqlDataAdapter
        Dim mySqlReader As MySqlDataReader
        If mySql.State = ConnectionState.Closed Then
            mySql.Open()
        End If

        mySqlCommand.CommandText = "Select * from  ambu where prov ='" & province.Text & "' and ampur = '" & amphur.Text & "' and tambul ='" & tambol.Text & "';"
        mySqlCommand.Connection = mySql
        mySqlAdaptor.SelectCommand = mySqlCommand


        Try
            mySqlReader = mySqlCommand.ExecuteReader
            While mySqlReader.Read()

                pm_key = mySqlReader("num")
                dr = mySqlReader("dr")
                rn = mySqlReader("rn")
                pn = mySqlReader("pn")
                na = mySqlReader("na")
                car = mySqlReader("car")
            End While

        Catch ex As Exception
            MsgBox(ex.ToString)

            mySql.Close()
        End Try

        mySql.Close()
    End Sub
    Public Sub Show_price()
        mySql.Close()

        If mySql.State = ConnectionState.Closed Then
            mySql.Open()
        End If
        Dim mySqlCommand As New MySqlCommand
        Dim mySqlAdaptor As New MySqlDataAdapter
        Dim mySqlReader As MySqlDataReader
        mySqlCommand.CommandText = "Select * from  ambu where prov ='" & province.Text & "';"
        mySqlCommand.Connection = mySql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader
            While mySqlReader.Read()
                pm_key = mySqlReader("num")
                dr = mySqlReader("dr")
                rn = mySqlReader("rn")
                pn = mySqlReader("pn")
                na = mySqlReader("na")
                car = mySqlReader("car")

            End While

        Catch ex As Exception

        End Try

        mySql.Close()
    End Sub

    Public Sub Show_price1()
        mySql.Close()
        If mySql.State = ConnectionState.Closed Then
            mySql.Open()
        End If
        Dim mySqlCommand As New MySqlCommand
        Dim mySqlAdaptor As New MySqlDataAdapter
        Dim mySqlReader As MySqlDataReader
        mySqlCommand.CommandText = "Select * from  ambu where prov ='" & province.Text & "'and ampur = '" & amphur.Text & "';"
        mySqlCommand.Connection = mySql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader
            While mySqlReader.Read()
                pm_key = mySqlReader("num")
                dr = mySqlReader("dr")
                rn = mySqlReader("rn")
                pn = mySqlReader("pn")
                na = mySqlReader("na")
                car = mySqlReader("car")

            End While

        Catch ex As Exception

        End Try

        mySql.Close()
    End Sub

    Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        price.Text = "0"
        If car_man.Checked = True Then
            price.Text = CInt(price.Text) + CInt(car)
        End If

        If rn_price.Checked = True Then
            price.Text = CInt(price.Text) + CInt(rn)
        End If
        If pn_price.Checked = True Then
            price.Text = CInt(price.Text) + CInt(pn)
        End If

        If na_price.Checked = True Then
            price.Text = CInt(price.Text) + CInt(na)
        End If
        If doctor_price.Checked = True Then
            price.Text = CInt(price.Text) + CInt(dr)
        End If

        If pale.Checked = True Then
            price.Text = CInt(price.Text) + CInt(na)
        End If
        price.Text = CInt(price.Text) * 2
    End Sub

    Public Sub default_fon()
        price.Text = ""
        car_man.Checked = False
        rn_price.Checked = False
        pn_price.Checked = False
        na_price.Checked = False
        doctor_price.Checked = False
        pale.Checked = False

    End Sub

    Private Sub txt_search_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txt_search.KeyDown
        Dim string_count As Integer = 0
        Dim string_redcell As String = " "
        Dim id_hn As String

        Dim mySqlCommand As New MySqlCommand
        Dim mySqlAdaptor As New MySqlDataAdapter
        Dim mySqlReader As MySqlDataReader
        Dim amp As String
        Dim prv As String
        Dim tmp As String
        Dim zipcode As String

        If e.KeyCode = "13" Then


            id_hn = txt_search.Text


            Dim MyODBCConnection As New OdbcConnection("dsn=RYHPFV5; Userid=mse;Password=m0116;")  'Open the connection
            Try
                MyODBCConnection.Open()
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try

            ' Dim selectCMD As OdbcCommand = New OdbcCommand("SELECT * FROM REGMASV5PF WHERE RMSHNNO='7708' and RMSHNYR = '56'")
            Dim selectCMD As OdbcCommand = New OdbcCommand("SELECT * FROM REGDETV5PF WHERE RDTHN = '" & id_hn & "'")

            selectCMD.Connection = MyODBCConnection


            Try
                'Set the mouse to show a Wait cursor
                Dim dr As OdbcDataReader = selectCMD.ExecuteReader
                'start the Read loop
                While dr.Read
                    'Note: the numbers in double quotes represent the column number from the AS400 database
                    'Add the data to the list view
                    txt_address.Text = dr.GetString(2).Trim
                    amp = dr.GetString(4)
                    prv = dr.GetString(3)
                    tmp = dr.GetString(5)
                    zipcode = dr.GetString(6)
                    'End the loop
                End While
                'Reset the cursor

            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try

            MyODBCConnection.Close()


            If MyODBCConnection.State = ConnectionState.Closed Then
                MyODBCConnection.Open()
            End If
            selectCMD = New OdbcCommand("SELECT RMSNAME,RMSSURNAM FROM REGMASV5PF WHERE RMSHNREF = '" & id_hn & "'")
            selectCMD.Connection = MyODBCConnection
            Try
                'Set the mouse to show a Wait cursor
                Dim dr As OdbcDataReader = selectCMD.ExecuteReader
                'start the Read loop
                While dr.Read
                    'Note: the numbers in double quotes represent the column number from the AS400 database
                    'Add the data to the list view
                    txt_name.Text = dr.GetString(0) + "  " + dr.GetString(1)
                    'End the loop
                End While
                'Reset the curso
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try




            MyODBCConnection.Close()

            If MyODBCConnection.State = ConnectionState.Closed Then
                MyODBCConnection.Open()
            End If

            selectCMD = New OdbcCommand("SELECT TPVPRVNAM,TMPAMPNAM,TBNTBNNAM FROM TABPRVV5PF,TABAMPV5PF,TABTBNV5PF WHERE TABPRVV5PF.TPVPRVCOD = TABAMPV5PF.TMPPRVCOD AND TABAMPV5PF.TMPAMPCOD = TABTBNV5PF.TBNAMPCOD AND TABAMPV5PF.TMPPRVCOD = TABTBNV5PF.TBNPRVCOD AND (TABAMPV5PF.TMPPRVCOD = '" & prv & "') AND (TABTBNV5PF.TBNTBNCOD = '" & tmp & "') AND (TABAMPV5PF.TMPAMPCOD = '" & amp & "')")

            selectCMD.Connection = MyODBCConnection




            Try
                'Set the mouse to show a Wait cursor
                Dim dr As OdbcDataReader = selectCMD.ExecuteReader
                'start the Read loop
                While dr.Read
                    If dr.GetString(2) IsNot DBNull.Value Then
                        txt_address.Text = txt_address.Text + " ตำบล " + dr.GetString(2).Trim

                    End If
                    If dr.GetString(1) IsNot DBNull.Value Then

                        txt_address.Text = txt_address.Text + " อำเภอ " + dr.GetString(1).Trim

                    End If
                    If dr.GetString(0) IsNot DBNull.Value Then
                        txt_address.Text = txt_address.Text + " จังหวัด " + dr.GetString(0).Trim

                    End If

                    txt_address.Text = txt_address.Text + " " + zipcode
                End While

            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try

        End If




    End Sub
End Class